with three_segment_province as
         (
             select provider_id,provider_desc  from jms_dim.dim_network_whole_massage group by  provider_id,provider_desc
         ),
     three_segment_city as
         (
             select city_id,city_desc  from jms_dim.dim_network_whole_massage group by  city_id,city_desc
         ),
     three_segment_area as
         (
             select
                 area_id,
                 area_desc,
                 area_first_code as area_first_code
             from (select  area_id,
                           area_desc
                   from jms_dim.dim_network_whole_massage
                   group by area_id, area_desc
                  ) t

                      left join ( select
                                      destination_area_id ,
                                      concat_ws(',',collect_set(code)) as area_first_code
                                  from
                                      jms_dim.dim_lmdm_sys_first_code t
                                  where
                                          t.is_enable = '1'
                                    and t.is_delete = '1'
                                  group by
                                      destination_area_id ) t1 on area_id=t1.destination_area_id
             group by area_id, area_desc,area_first_code
         ),
     three_segment_different as (
         select express_org_code, express_center_short_code
         from jms_dim.dim_yl_sys_package_different_base
         group by express_org_code, express_center_short_code
     ),

     three_segment_site as
         (
             select
                 t1.name,
                 t1.code,
                 t1.agent_code,
                 t1.agent_name,
                 t1.provider_id,
                 t1.provider_desc,
                 t1.city_id,
                 t1.city_desc,
                 t1.area_id,
                 t1.area_desc,
                 max(t2.express_center_short_code) as first_code_package,
                 max(t3.code) as sj_second_code,
                 concat_ws(',',collect_set(t4.code)) as first_code

             from
                 jms_dim.dim_network_whole_massage t1
                     left join three_segment_different t2 on t1.code=t2.express_org_code
                     left join jms_dim.dim_lmdm_sys_second_code t3 on t1.code=t3.network_code and  t3.is_enable='1' and t3.is_delete='1'
                     left join jms_dim.dim_lmdm_sys_first_code t4 on t1.provider_id=t4.destination_province_id
                     and t1.city_id=t4.destination_city_id and t1.area_id =t4.destination_area_id
                     and  t4.is_enable='1' and t4.is_delete='1'

             group by
                 t1.name,
                 t1.code,
                 t1.agent_code,
                 t1.agent_name,
                 t1.provider_id,
                 t1.provider_desc,
                 t1.city_id,
                 t1.city_desc,
                 t1.area_id,
                 t1.area_desc
         ),deliver_bill as (
    select
        waybill_no,
        max(t4.tail_code) as tail_code
    from
        jms_dwd.dwd_tab_barscan_deliver_base_dt t
         join  (
            select  t1.staff_code,
                    t1.tail_code
            from jms_dim.dim_lmdm_sys_tail_code t
                     join jms_dim.dim_lmdm_sys_tail_code_staff t1 on t.id=t1.tail_id
            where   t.is_enable='1' and t.is_delete='1'
            group by  t1.staff_code, t1.tail_code
        ) t4 on t.box_num=t4.staff_code
    where
        t.dt between '{{ execution_date | date_add(-30) | cst_ds }}'  AND '{{ execution_date | cst_ds }}'
    group by waybill_no
),
     three_segment_bill as (
         select
             t1.waybill_no --运单号
              ,t1.deliver_later_scansitecode --派件网点
              ,t2.agent_name as deliver_agent_name
              ,t2.agent_code as deliver_agent_code
              ,t2.name as deliver_name
              ,t2.code as deliver_code
              ,t1.dispatch_staff_name as deliver_later_user  --派件人
              ,t1.dispatch_staff_code as deliver_later_user_code  --派件人code
              ,t1.is_abnormal --问题就标识
              --,t1.sign_later_scantime --签收时间
              ,nvl(t1.sign_later_scantime ,t1.outstore_later_scantime) as sign_later_scantime--签收时间
              ,t2.first_code_package
              ,t2.sj_second_code
              ,t2.first_code
              ,nvl(t5.tail_code,t4.tail_code)  as sj_third_code
              ,case when t2.first_code_package is not  null then t2.first_code_package else t2.first_code end as sj_first_code
              ,t1.sign_later_scansitecode --签收网点
              ,t3.name as sign_later_scansite --签收网点
              ,dt
         from
             jms_dwd.dwd_wide_sign_summary_waybill_dt t1
                 left join  three_segment_site t2 on t1.deliver_later_scansitecode=t2.code
                 left join  three_segment_site t3 on t1.sign_later_scansitecode=t3.code
                 left join  (
                             select  t1.staff_code,
                                     t1.tail_code
                             from jms_dim.dim_lmdm_sys_tail_code t
                                      join jms_dim.dim_lmdm_sys_tail_code_staff t1 on t.id=t1.tail_id
                             where   t.is_enable='1' and t.is_delete='1'
                             group by  t1.staff_code, t1.tail_code
                         ) t4 on t1.dispatch_staff_code=t4.staff_code
                  left join deliver_bill t5 on t1.waybill_no=t5.waybill_no


         where t1.dt between '{{ execution_date | date_add(-30) | cst_ds }}'  AND '{{ execution_date | cst_ds }}'  and t1.is_reback=0
                     and ( t1.taking_later_scansitecode is null or t1.sign_later_scansitecode is null or t1.taking_later_scansitecode<>t1.sign_later_scansitecode )

     ),
     three_segment_order as (
         select
             t1.id
              ,t1.waybill_id as waybill_no
              ,t1.input_time
              ,t1.order_source_name
              ,t1.order_source_code
              ,t1.real_pick_network_code --取件网点
              ,t5.name as real_pick_name
              ,t5.code   as real_pick_code
              ,t1.first_code
              ,t1.second_code
              ,t1.third_code

              ,t1.receiver_province_id --收件人
              ,t2.provider_desc
              ,t1.receiver_city_id
              ,t3.city_desc
              ,t1.receiver_area_id
              ,t4.area_desc
              ,if(locate(t1.first_code, t4.area_first_code)>0, t1.first_code,null) as area_first_code
              ,t1.receiver_detailed_address
              ,t1.dispatch_network_code
              ,t6.name as dispatch_name
              ,t6.code   as dispatch_code

         from
             jms_dwd.dwd_yl_oms_oms_order_dt t1
--                  left join three_segment_province t2 on t1.receiver_province_id=t2.provider_id
--                  left join three_segment_city t3 on t1.receiver_city_id=t3.city_id
--                  left join three_segment_area t4 on t1.receiver_area_id=t4.area_id
--                  left join three_segment_site t5 on t1.real_pick_network_code=t5.code
--                  left join three_segment_site t6 on t1.dispatch_network_code=t6.code

                 left join three_segment_province t2 on if(t1.receiver_province_id is null , t1.waybill_id,t1.receiver_province_id)=t2.provider_id
                 left join three_segment_city t3 on if(t1.receiver_city_id is null , t1.waybill_id,t1.receiver_city_id) =t3.city_id
                 left join three_segment_area t4 on if(t1.receiver_area_id is null , t1.waybill_id,t1.receiver_area_id) =t4.area_id
                 left join three_segment_site t5 on if(t1.real_pick_network_code is null or
                                                            INSTR(t1.real_pick_network_code,'1970-01')>0, t1.waybill_id,t1.real_pick_network_code)=t5.code
                 left join three_segment_site t6 on if(t1.dispatch_network_code is null , t1.waybill_id,t1.dispatch_network_code)=t6.code


         where t1.dt between '{{ execution_date | date_add(-35) | cst_ds }}' AND '{{ execution_date | cst_ds }}'
     )

insert overwrite table jms_dm.dm_lmdm_three_segment_monitoring_detail_dt partition (dt)
select
    t.order_id
     ,t.input_time
     ,t.waybill_no
     ,t.order_source_code
     ,t.order_source_name
     ,t.real_pick_name
     ,t.real_pick_code
     ,t.receiver_province_id
     ,t.receiver_province_desc  as receiver_province_desc
     ,t.receiver_city_id
     ,t.receiver_city_desc as receiver_city_desc
     ,t.receiver_area_id
     ,t.receiver_area_desc as receiver_area_desc
     ,t.receiver_detailed_address
     ,t.dispatch_name
     ,t.dispatch_code
     ,t.deliver_agent_name
     ,t.deliver_agent_code
     ,t.deliver_name
     ,t.deliver_code
     ,t.deliver_later_user  --派件人
     ,t.deliver_later_user_code  --派件人code
     ,t.is_abnormal --问题就标识
     ,t.first_code
     ,t.second_code
     ,t.third_code
     ,t.sign_later_scantime --签收时间
     ,t.first_code_fail
     ,t.first_code_error
     ,t.second_code_fail
     ,t.second_code_error
     ,t.third_code_fail
     ,t.third_code_error
     ,t.sign_later_scansitecode
     ,t.sign_later_scansite
     ,t.sj_first_code
     ,t.sj_second_code
     ,t.sj_third_code
     ,t.dt
from  (
          select
              t2.id as order_id
               ,row_number() over(partition by t1.dt,t2.waybill_no order by t1.dt desc) as rnk
               ,t2.input_time
               ,t2.waybill_no
               ,t2.order_source_code
               ,t2.order_source_name
               ,t2.real_pick_name
               ,t2.real_pick_code
               ,t2.receiver_province_id
               ,t2.provider_desc  as receiver_province_desc
               ,t2.receiver_city_id
               ,t2.city_desc as receiver_city_desc
               ,t2.receiver_area_id
               ,t2.area_desc as receiver_area_desc
               ,t2.receiver_detailed_address
               ,t2.dispatch_name
               ,t2.dispatch_code
               ,t1.deliver_agent_name
               ,t1.deliver_agent_code
               ,t1.deliver_name
               ,t1.deliver_code
               ,t1.deliver_later_user  --派件人
               ,t1.deliver_later_user_code  --派件人code
               ,t1.is_abnormal --问题就标识
               ,t2.first_code
               ,t2.second_code
               ,t2.third_code
               ,t1.sign_later_scantime --签收时间
               ,case when t2.first_code is null then 1 else 0 end  as first_code_fail
--         ,case when t2.first_code is not null and t2.first_code <> t1.sj_first_code then 1 else 0 end  as first_code_error
               ,case when t2.first_code is not null and  t2.first_code <> if(t2.area_first_code is not null,t2.area_first_code,if((regexp_replace(t2.second_code, '[A-Za-z]', '') =  regexp_replace(t1.sj_second_code, '[A-Za-z]', '')),t2.first_code,'0-a')) then 1 else 0 end  as first_code_error

               ,case when t2.second_code is null then 1 else 0 end  as second_code_fail
--         ,case when t2.second_code is not null  and ((  t2.first_code <> t1.sj_first_code ) or (t2.first_code == t1.sj_first_code and t2.second_code <> t1.sj_second_code) ) then 1 else 0 end as second_code_error
               ,case when t2.second_code is not null  and (regexp_replace(t2.second_code, '[A-Za-z]', '') <>  regexp_replace(t1.sj_second_code, '[A-Za-z]', '')) then 1 else 0 end as second_code_error
               ,case when t2.third_code='000' or  t2.third_code is null then 1 else 0 end  as third_code_fail
--         ,case when (t2.third_code is not null  and t2.third_code <> '000') and  t2.third_code <> t1.sj_third_code then 1 else 0 end  as third_code_error
               ,case when ((t2.third_code is not null  and t2.third_code <> '000' and t1.sj_third_code is not null) and  t2.third_code <> t1.sj_third_code ) then 1 else 0 end  as third_code_error

               ,t1.sign_later_scansitecode
               ,t1.sign_later_scansite
               ,if(t2.area_first_code is not null,t2.area_first_code,if((regexp_replace(t2.second_code, '[A-Za-z]', '') =  regexp_replace(t1.sj_second_code, '[A-Za-z]', '')),t2.first_code,null))   sj_first_code
               ,t1.sj_second_code
               ,t1.sj_third_code
               ,t1.dt
          from three_segment_bill t1
                   left join three_segment_order t2 on t1.waybill_no=t2.waybill_no
          where t2.id is not null
            and t1.dt = '{{ execution_date | cst_ds }}'
      )  t
where t.rnk=1

 distribute by pmod(hash(rand()), 249);